package com.zhou.starter.jpacomment.service.impl;

import com.zhou.starter.jpacomment.service.AlterCommentService;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author lcz
 * @desc 修改表注释和字段注释
 * @mail lcz_0130@163.com
 * @date 2024/03/01
 */

public class KingBaseAlterCommentServiceImpl implements AlterCommentService {
    private String schema;

    @Override
    public void setSchema(String schema) {
        this.schema = schema;
    }

    private JdbcTemplate jdbcTemplate;

    @Override
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 1SCHEMA 2表名称 3注释
     */
    String updateTableComment = "COMMENT ON TABLE %s IS '%s';";

    /**
     * 1SCHEMA 2表名称 3字段名称 4字段注释
     */
    String updateColumnComment = "COMMENT ON COLUMN %s.%s IS '%s'";

    @Override
    public void alterTableComment(String tableName, String tableComment) {
        jdbcTemplate.update(String.format(updateTableComment, tableName.toUpperCase(), tableComment));
    }

    @Override
    public void alterColumnComment(String tableName, String columnName, String columnComment) {
        jdbcTemplate.update(String.format(updateColumnComment, tableName.toUpperCase(), columnName.toUpperCase(), columnComment));
    }

    @Override
    public String getSchema() {
        return schema;
    }

    @Override
    public String hasTableComment(String tableName) {
        tableName = tableName.substring(tableName.indexOf(".") + 1);
//        return jdbcTemplate.queryForObject("SELECT comments FROM user_tab_comments WHERE table_name = ? ", String.class, tableName);
        List<String> commentsList = jdbcTemplate.query(
                "SELECT comments FROM user_tab_comments WHERE table_name = ?",
                new Object[]{tableName},
                (rs, rowNum) -> rs.getString("comments")
        );
        if (!commentsList.isEmpty()){
            //删除null或者空字符串
            commentsList.removeIf(s -> s == null || s.trim().isEmpty());
            return commentsList.toString();
        }
        return null;
    }

    @Override
    public String hasColumnComment(String tableName, String columnName) {
        tableName = tableName.substring(tableName.indexOf(".") + 1);
        return jdbcTemplate.queryForObject("SELECT comments FROM user_col_comments WHERE table_name = ? AND column_name = ? ", String.class, tableName, columnName);
    }
}
